﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NHibernate;
using DotNet.Common.NHibernateUtil;
using ZLCMS.Data.Model;
using ZLCMS.Commands.Data;
using Newtonsoft.Json;
using System.Collections;
using System.Threading;

namespace ZLCMS.Web.Tools
{
    public partial class UpdateBill : System.Web.UI.Page
    {

        protected string BillDate;
        protected Hashtable supplyHash;

        protected void Page_Load(object sender, EventArgs e)
        {
            string key = "F8Zn1mZM4qe8IQ5r";
            if (Request["key"] != key)
            {
                Response.Write("param error");
                return;
            }

            string bd = Request["bd"];
            string ed = Request["ed"];

            if (string.IsNullOrEmpty(bd) || string.IsNullOrEmpty(ed))
            {
                Response.Write("日期错误");
                return;
            }

            int days = (DateTime.Parse(ed) - DateTime.Parse(bd)).Days;

            string step = Request["step"];
            string proc = Request["proc"];


            Thread thread = new Thread(delegate()
            {

                //记录支付成功的订单
                using (ISession session = SessionFactoryHelper.CreateSession())
                {
                    //获取产品供货价
                    InitSupplyData(session);

                    if (step == "1")
                    {
                        CalcBillProductDetail(session, bd, days, proc);
                    }
                    else if (step == "2")
                    {
                        ClearDailyProductBillOldData(session);

                        CalcDailyProductBill(session);
                    }
                    else if (step == "3")
                    {
                        ClearDailyBill(session);

                        CalcDailyBill(session);
                    }
                    else if (step == "4")
                    {
                        CalcWeeklyBill(session, bd, ed);
                    }
                    else if (step == "9")
                    {
                        ClearData(session, bd, ed);
                    }
                }


            });
            thread.Start();

            Response.Write("success");

        }

        private void ClearData(ISession session, string bd, string ed)
        {
            session.CreateSQLQuery(" delete from shop_bill_product_detail where opt_date >= :bdate and opt_date<= :edate ")
                   .SetParameter("bdate", bd)
                   .SetParameter("edate", ed)
                   .ExecuteUpdate();

            session.CreateSQLQuery(" delete from shop_bill_product where opt_date >= :bdate and opt_date<= :edate ")
                   .SetParameter("bdate", bd)
                   .SetParameter("edate", ed)
                   .ExecuteUpdate();

            session.CreateSQLQuery(" delete from shop_bill_info_daily where bill_date >= :bdate and bill_date<= :edate ")
                   .SetParameter("bdate", bd)
                   .SetParameter("edate", ed)
                   .ExecuteUpdate();

        }

        private void CalcBillProductDetail(ISession session, string bd, int days, string proc)
        {
            for (int i = 0; i <= days; i++)
            {
                BillDate = DateTime.Parse(bd).AddDays(i).ToString("yyyy-MM-dd");


                if (proc == "clear" || proc == "all")
                {
                    ClearProductDetailOldData(session);
                }
                if (proc == "sale" || proc == "all")
                {
                    CalcSaleProductDetail(session);
                }
                if (proc == "cancel" || proc == "all")
                {
                    CalcCancelProductDetail(session);
                }
                if (proc == "refund" || proc == "all")
                {
                    CalcRefundProductDetail(session);
                }
            }
        }

        /// <summary>
        /// 获取产品供货价
        /// </summary>
        /// <param name="session"></param>
        private void InitSupplyData(ISession session)
        {
            var supplylist = session.CreateQuery("from ShopProductSupplyInfo where Status <> '9' ")
                                    .List<ShopProductSupplyInfo>();

            supplyHash = new Hashtable();

            foreach (var supplyItem in supplylist)
            {
                supplyHash[supplyItem.ProductSkuCode] = supplyItem.SupplyFee;
            }
        }

        /// <summary>
        /// S4-汇总每周账单数据
        /// </summary>
        /// <param name="session"></param>
        private void CalcWeeklyBill(ISession session, string BDate, string EDate)
        {
            session.CreateSQLQuery(" insert into shop_bill_info(begin_date, end_date, dept_code, sale_fee, cancel_fee, refund_fee, final_sale_fee, final_cancel_fee, final_refund_fee, status) " +
                                   " select :bdate, :edate, dept_code,sum(sale_fee), sum(cancel_fee) ,sum(refund_fee) ,sum(final_sale_fee),sum(final_cancel_fee) ,sum(final_refund_fee), '0' " +
                                   " FROM shop_bill_info_daily where bill_date >= :bdate and bill_date<= :edate group by dept_code ")
                   .SetParameter("bdate", BDate)
                   .SetParameter("edate", EDate)
                   .ExecuteUpdate();

            session.CreateSQLQuery(" update shop_bill_info set total_fee = final_sale_fee - final_cancel_fee -final_refund_fee, paid_fee = 0" +
                                   " where begin_date >= :bdate and end_date<= :edate ")
                   .SetParameter("bdate", BDate)
                   .SetParameter("edate", EDate)
                   .ExecuteUpdate();
        }

        /// <summary>
        /// S3-清空历史数据
        /// </summary>
        /// <param name="session"></param>
        private void ClearDailyBill(ISession session)
        {
            session.CreateSQLQuery(" delete from shop_bill_info_daily where status = '0' ").ExecuteUpdate();
        }

        /// <summary>
        /// S3-计算每日的订单，汇总成账单
        /// </summary>
        /// <param name="session"></param>
        private void CalcDailyBill(ISession session)
        {
            session.CreateSQLQuery(" insert into shop_bill_info_daily(bill_date, dept_code, status) " +
                                   " select  opt_date, dept_code, '0' FROM shop_bill_product s where bill_id = 0 group by dept_code, opt_date; ").ExecuteUpdate();


            var list = session.CreateQuery(" FROM ShopBillInfoDaily where Status = '0' ")
                              .List<ShopBillInfoDaily>();


            foreach (var item in list)
            {
                item.SaleFee = session.CreateSQLQuery(" select sum(total_fee) FROM shop_bill_product" +
                                       " where dept_code = :dcode and opt_date=:optdate and bill_kind='SALE' ")
                       .SetParameter("dcode", item.DeptCode)
                       .SetParameter("optdate", item.BillDate)
                       .UniqueResult<decimal>();

                item.FinalSaleFee = session.CreateSQLQuery(" select sum(final_total_fee) FROM shop_bill_product" +
                                       " where dept_code = :dcode and opt_date=:optdate and bill_kind='SALE' ")
                       .SetParameter("dcode", item.DeptCode)
                       .SetParameter("optdate", item.BillDate)
                       .UniqueResult<decimal>();

                item.CancelFee = session.CreateSQLQuery(" select sum(total_fee) FROM shop_bill_product" +
                                       " where dept_code = :dcode and opt_date=:optdate and bill_kind='CANCEL' ")
                       .SetParameter("dcode", item.DeptCode)
                       .SetParameter("optdate", item.BillDate)
                       .UniqueResult<decimal>();

                item.FinalCancelFee = session.CreateSQLQuery(" select sum(final_total_fee) FROM shop_bill_product" +
                                       " where dept_code = :dcode and opt_date=:optdate and bill_kind='CANCEL' ")
                       .SetParameter("dcode", item.DeptCode)
                       .SetParameter("optdate", item.BillDate)
                       .UniqueResult<decimal>();

                item.RefundFee = session.CreateSQLQuery(" select sum(total_fee) FROM shop_bill_product" +
                                       " where dept_code = :dcode and opt_date=:optdate and bill_kind='REFUND' ")
                       .SetParameter("dcode", item.DeptCode)
                       .SetParameter("optdate", item.BillDate)
                       .UniqueResult<decimal>();

                item.FinalRefundFee = session.CreateSQLQuery(" select sum(final_total_fee) FROM shop_bill_product" +
                                       " where dept_code = :dcode and opt_date=:optdate and bill_kind='REFUND' ")
                       .SetParameter("dcode", item.DeptCode)
                       .SetParameter("optdate", item.BillDate)
                       .UniqueResult<decimal>();

                item.Status = "1";
                item.TotalFee = item.FinalSaleFee - item.FinalCancelFee - item.FinalRefundFee;
                session.Update(item);
                session.Flush();

                session.CreateSQLQuery(" update shop_bill_product set bill_id = :id where dept_code = :dcode and opt_date=:optdate and bill_id = 0 ")
                       .SetParameter("id", item.Id)
                       .SetParameter("dcode", item.DeptCode)
                       .SetParameter("optdate", item.BillDate)
                       .ExecuteUpdate();

                session.CreateSQLQuery(" update shop_bill_product_detail set bill_id = :id where dept_code = :dcode and opt_date=:optdate and bill_id = 0 ")
                       .SetParameter("id", item.Id)
                       .SetParameter("dcode", item.DeptCode)
                       .SetParameter("optdate", item.BillDate)
                       .ExecuteUpdate();
            }

        }
        
        private void ClearDailyProductBillOldData(ISession session)
        {
            //删除没有归档记录
            session.CreateSQLQuery("delete from shop_bill_product where bill_id = 0 ")
                   .ExecuteUpdate();
        }

        /// <summary>
        /// S2-生成每日产品汇总
        /// </summary>
        /// <param name="session"></param>
        private void CalcDailyProductBill(ISession session)
        {
            session.CreateSQLQuery(" insert into shop_bill_product ( bill_id,bill_kind,opt_date,product_no ,sku_product_no,total_quantity, total_fee,final_total_fee,  dept_code ) "+
                                   " select 0, bill_kind, opt_date,product_no ,sku_product_no, sum(quantity), sum( total_fee ), sum( final_total_fee ), dept_code "+
                                   " from Shop_Bill_Product_Detail where bill_id = 0 "+
                                   " group by  bill_kind, opt_date,product_no ,sku_product_no, dept_code ").ExecuteUpdate();
        }

        /// <summary>
        /// S1-清除规定时间内订单数据
        /// </summary>
        /// <param name="session"></param>
        private void ClearProductDetailOldData(ISession session)
        {
            //特殊标记已经归档记录
            session.CreateSQLQuery("update Shop_Bill_Product_Detail set bill_id = -bill_id where opt_date = :optdate and bill_id <> 0 ")
                   .SetParameter("optdate", BillDate)
                   .ExecuteUpdate();

            //删除没有归档记录
            session.CreateSQLQuery("delete from Shop_Bill_Product_Detail where bill_id = 0 and opt_date = :optdate ")
                   .SetParameter("optdate", BillDate)
                   .ExecuteUpdate();
        }

        /// <summary>
        /// S1-计算规定时间内，已经支付，但执行取消的订单产品数据
        /// </summary>
        /// <param name="session"></param>
        private void CalcCancelProductDetail(ISession session)
        {
            ShopOrderInfo orderInfo;
            IList<ShopOrderProduct> orderProductList;

            ShopBillProductDetail billPD;
            decimal fianlUF;
            //IList<SKUGroup> skuList;
            //string skuString;
            
            var list = session.CreateSQLQuery("select Id FROM shop_order_info where pay_status = '1' and Is_Cancelled = '1' and order_time >= :bdate and order_time <= :edate")
                              .SetParameter("bdate", DateTime.Parse(BillDate + " 00:00:00"))
                              .SetParameter("edate", DateTime.Parse(BillDate + " 23:59:59"))
                              .List<int>();

            foreach (var item in list)
            {
                orderInfo = session.Get<ShopOrderInfo>(item);

                orderProductList = session.CreateQuery("from ShopOrderProduct where OrderNo = :orderno ")
                                          .SetParameter("orderno", orderInfo.OrderNo)
                                          .List<ShopOrderProduct>();

                foreach (var opItem in orderProductList)
                {
                    //skuList = JsonConvert.DeserializeObject<List<SKUGroup>>(opItem.SkuData);

                    //skuString = null;

                    //foreach (var skuItem in skuList)
                    //{
                    //    skuString += skuItem.ItemName + ",";
                    //}

                    fianlUF = supplyHash[opItem.SkuProductNo] != null ? (decimal)supplyHash[opItem.SkuProductNo] : 0;

                    billPD = new ShopBillProductDetail()
                    {
                        BillId = 0,
                        BillKind = "CANCEL",
                        DeptCode = orderInfo.DeptCode,
                        OptDate = string.Format("{0:yyyy-MM-dd}", orderInfo.CancelTime),
                        TradeNo = orderInfo.OrderNo,
                        ProductNo = opItem.ProductNo,
                        SkuProductNo = opItem.SkuProductNo,
                        //ProductName = opItem.ProductName,
                        UnitFee = (decimal)opItem.UnitPrice,
                        TotalFee = (decimal)opItem.TotalPrice,
                        Quantity = opItem.Quantity,
                        FinalTotalFee = fianlUF * opItem.Quantity,
                        FinalUnitFee = fianlUF
                        //SkuProductName = skuString
                    };

                    session.Save(billPD);
                    session.Flush();
                }
            }
        }

        /// <summary>
        /// S1-计算规定时间内，支付成功的订单产品数据
        /// </summary>
        /// <param name="session"></param>
        private void CalcSaleProductDetail(ISession session)
        {
            ShopOrderInfo orderInfo;
            IList<ShopOrderProduct> orderProductList;

            ShopBillProductDetail billPD;
            decimal fianlUF;
            //IList<SKUGroup> skuList;
            //string skuString;


            var list = session.CreateSQLQuery("select Id FROM shop_order_info where pay_status = '1' and order_time >= :bdate and order_time <= :edate")
                              .SetParameter("bdate", DateTime.Parse(BillDate + " 00:00:00"))
                              .SetParameter("edate", DateTime.Parse(BillDate + " 23:59:59"))
                              .List<int>();
            
            foreach (var item in list)
            {
                orderInfo = session.Get<ShopOrderInfo>(item);

                orderProductList = session.CreateQuery("from ShopOrderProduct where OrderNo = :orderno ")
                                          .SetParameter("orderno", orderInfo.OrderNo)
                                          .List<ShopOrderProduct>();

                foreach (var opItem in orderProductList)
                {
                    //skuList = JsonConvert.DeserializeObject<List<SKUGroup>>(opItem.SkuData);

                    //skuString = null;

                    //foreach (var skuItem in skuList)
                    //{
                    //    skuString += skuItem.ItemName + ",";
                    //}

                    fianlUF =  supplyHash[opItem.SkuProductNo] != null ? (decimal)supplyHash[opItem.SkuProductNo] : 0;

                    billPD = new ShopBillProductDetail()
                    {
                        BillId = 0,
                        BillKind = "SALE",
                        DeptCode = orderInfo.DeptCode,
                        OptDate = string.Format("{0:yyyy-MM-dd}", orderInfo.OrderTime),
                        TradeNo = orderInfo.OrderNo,
                        ProductNo = opItem.ProductNo,
                        SkuProductNo = opItem.SkuProductNo,
                        //ProductName = opItem.ProductName,
                        UnitFee = (decimal)opItem.UnitPrice,
                        TotalFee = (decimal)opItem.TotalPrice,
                        Quantity = opItem.Quantity,
                        FinalTotalFee = fianlUF * opItem.Quantity,
                        FinalUnitFee = fianlUF
                        //SkuProductName = skuString
                    };

                    session.Save(billPD);
                    session.Flush();
                }
            }
        }

        /// <summary>
        /// S1-计算规定时间内，售后退款的产品数据
        /// </summary>
        /// <param name="session"></param>
        private void CalcRefundProductDetail(ISession session)
        {
            //受理中状态的记录，即进行统计
            var list = session.CreateSQLQuery("select RECID FROM shop_order_refund_info " + 
                                              " where confirm_status = '1' and cancel_status = '0' and process_status = '1' and refund_kind= 'REFUND' "+
                                              " and process_date >= :bdate and process_date <= :edate ")
                              .SetParameter("bdate", DateTime.Parse(BillDate + " 00:00:00"))
                              .SetParameter("edate", DateTime.Parse(BillDate + " 23:59:59"))
                              .List<int>();

            ShopOrderRefundInfo refundInfo;
            IList<ShopOrderRefundProduct> refundProductList;

            ShopBillProductDetail billPD;

            foreach (var item in list)
            {
                refundInfo = session.Get<ShopOrderRefundInfo>(item);

                refundProductList = session.CreateQuery("from ShopOrderRefundProduct where RefundNo = :reno ")
                                          .SetParameter("reno", refundInfo.RefundNo)
                                          .List<ShopOrderRefundProduct>();

                foreach (var opItem in refundProductList)
                {
                    //skuList = JsonConvert.DeserializeObject<List<SKUGroup>>(opItem.SkuData);

                    //skuString = null;

                    //foreach (var skuItem in skuList)
                    //{
                    //    skuString += skuItem.ItemName + ",";
                    //}

                    billPD = new ShopBillProductDetail()
                    {
                        BillId = 0,
                        BillKind = "REFUND",
                        DeptCode = refundInfo.DeptCode,
                        OptDate = string.Format("{0:yyyy-MM-dd}", refundInfo.ProcessDate),
                        TradeNo = refundInfo.RefundNo,
                        ProductNo = opItem.ProductNo,
                        SkuProductNo = opItem.ProductSkuNo,
                        //ProductName = opItem.ProductName,
                        UnitFee = (decimal)opItem.RefundFee / opItem.Quantity,
                        TotalFee = (decimal)opItem.RefundFee,
                        Quantity = opItem.Quantity,
                        FinalUnitFee = (decimal)opItem.RefundFee / opItem.Quantity,
                        FinalTotalFee = (decimal)opItem.RefundFee
                    };

                    session.Save(billPD);
                    session.Flush();
                }
            }
        }
    }
}